﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using Domain.Entity;
using Domain.Entity.bs;
using Domain.Entity.zct;
using Domain.IRepository;

namespace EFCore.Repository
{
    public class DepartmentRepository : IDepartmentRepository
    {
        //连接字符串
        string con = "Data Source=121.37.93.71;Initial Catalog=CSSL_ZCTWMS;User ID=sa;Password=geili@2025;Encrypt=False";

        public async Task<int> AddDepartment(Department department)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                var sql = @"
                INSERT INTO zct_department (
                    department_code,department_name,department_status,department_sort,department_parentid,is_delete,remark
                ) VALUES (
                    @department_code,@department_name,@department_status,@department_sort,@department_parentid,@is_delete,@remark
                );";

                var parameters = new DynamicParameters();
                parameters.Add("@department_code", department.Department_code);
                parameters.Add("@department_name", department.Department_name);
                parameters.Add("@department_status", department.Department_status);
                parameters.Add("@department_sort", department.Department_sort);
                parameters.Add("@department_parentid", department.Department_parentid);
                parameters.Add("@is_delete", 0);
                parameters.Add("@remark", department.Remark);

                return await sqlConnection.ExecuteAsync(sql, parameters);
            }
        }

        //删除部门信息
        public async Task<int> DeleteDepartment(int id)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                // 1. 检查部门是否存在且未被删除
                var checkExistsSql = "SELECT COUNT(1) FROM zct_department WHERE department_id = @id AND is_delete = 0";
                var exists = await sqlConnection.QuerySingleAsync<int>(checkExistsSql, new { id });
                if (exists == 0)
                {
                    return -1; // 部门不存在或已被逻辑删除
                }

                // 2. 检查是否有未删除的引用记录
                // 注意：根据你的表结构，字段名是 department_id
                var checkSql = @"
                -- 检查销售退货单
                SELECT COUNT(*) FROM zct_user WHERE department_id = @id AND is_delete = 0";

                var counts = await sqlConnection.QueryAsync<int>(checkSql, new { id });

                // 如果任何一个表有引用记录，都不能删除
                if (counts.Any(count => count > 0))
                {
                    return 0; // 有引用，删除失败
                }

                // 3. 没有引用，执行逻辑删除
                var updateSql = @"
                UPDATE zct_department
                SET is_delete = 1
                WHERE is_delete = 0 AND(department_id = @id OR department_parentid = @id)"; 

                var rowsAffected = await sqlConnection.ExecuteAsync(updateSql, new { id });
                // 理论上 rowsAffected 应该是 1，但如果并发情况下已被其他请求删除，则可能是 0
                return rowsAffected > 0 ? 1 : -1;
            }
        }

        //查询所有部门信息
        public async Task<List<Department>> GetDepartments()
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = "select department_id, department_name from [dbo].[zct_department] where is_delete=0";
                return sqlConnection.Query<Department>(sql).ToList();
            }
        }

        public async Task<FenYe<Department>> GetDepartmentsFenYe(FenYe<Department> fenYe, string? Department_code, string? Department_name, int? Department_status)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                // 1. 首先获取所有满足条件的部门ID（包括子部门）
                var conditionBuilder = new StringBuilder("is_delete = 0");
                if (!string.IsNullOrEmpty(Department_code))
                {
                    conditionBuilder.Append($" AND (Department_code LIKE '%{Department_code}%' OR Department_name LIKE '%{Department_name}%')");
                }
                if (Department_status.HasValue)
                {
                    conditionBuilder.Append($" AND Department_status = {Department_status}");
                }

                // 2. 递归查找所有父部门ID
                string parentQuery = $@"
                    WITH ParentCTE AS (
                        SELECT department_id, department_parentid
                        FROM zct_department
                        WHERE {conditionBuilder}
                        UNION ALL
                        SELECT p.department_id, p.department_parentid
                        FROM zct_department p
                        INNER JOIN ParentCTE cte ON p.department_id = cte.department_parentid
                        WHERE p.is_delete = 0
                    )
                    SELECT DISTINCT department_id FROM ParentCTE";

                var parentIds = await sqlConnection.QueryAsync<int>(parentQuery);
                string idList = string.Join(",", parentIds);

                // 3. 重新构建查询条件（包含所有相关部门）
                string finalWhere = $"is_delete = 0 AND department_id IN ({idList})";

                // 4. 使用分页存储过程查询
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@pageSize", 10);
                parameters.Add("@keyName", "Department_id");
                parameters.Add("@tabelName", "zct_department");
                parameters.Add("@where", finalWhere);
                parameters.Add("@currentPage", fenYe.CurrentPage);
                parameters.Add("@rows", 0, DbType.Int32, ParameterDirection.Output);

                string sql = "exec [dbo].[Fenye] @pageSize, @keyName, @tabelName, @where, @currentPage, @rows output";
                var result = await sqlConnection.QueryAsync<Department>(sql, parameters);

                fenYe.List = result.ToList();
                fenYe.Rows = parameters.Get<int>("@rows");
                return fenYe;
            }
        }

        public async Task<Department> GetDepartmentsID(int id)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = "select department_code,department_name,department_status,department_sort,department_parentid,is_delete,remark from [dbo].[zct_department] where is_delete=0 AND Department_id = @id";

                var parameters = new DynamicParameters();
                parameters.Add("@id", id);

                return await sqlConnection.QueryFirstOrDefaultAsync<Department>(sql, parameters);
            }
        }

        public async Task<int> UpdateDepartments(Department department)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                var sql = @"
                UPDATE zct_department
                SET 
                    department_name = @department_name,
                    department_status = @department_status,
                    department_sort = @department_sort,
                    department_parentid = @department_parentid,
                    is_delete = @is_delete,
                    remark = @remark
                WHERE department_id = @department_id;";

                var parameters = new DynamicParameters();
                parameters.Add("@department_name", department.Department_name);
                parameters.Add("@department_status", department.Department_status);
                parameters.Add("@department_sort", department.Department_sort);
                parameters.Add("@department_parentid", department.Department_parentid);
                parameters.Add("@is_delete", department.Is_delete);
                parameters.Add("@remark", department.Remark);
                parameters.Add("@department_id", department.Department_id);

                return await sqlConnection.ExecuteAsync(sql, parameters);
            }
        }
    }
}
